package com.vca.common.utils;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.vca.common.constants.Constants;
import com.vca.common.exception.VcaException;
import com.vca.common.model.export.CourseSchedulingExport;
import com.vca.common.vo.*;
import lombok.SneakyThrows;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.BeanUtils;

import java.io.*;
import java.math.BigInteger;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

/**
 * 导出工具类
 */
public class ExportUtil {

    /**
     * 导出Excel文件
     *
     * @param fileName 文件名
     * @param title    文件标题
     * @param voList   数据列表
     * @param aliasMap 别名Map（别名需要与数据列表的数据对应）
     * @return 返回给前端的文件名（路径+文件名）
     */
    public static String exportExecl(String fileName, String title, List<?> voList, LinkedHashMap<String, String> aliasMap) {
        if (StrUtil.isBlank(fileName)) {
            throw new VcaException("文件名不能为空");
        }
        if (StrUtil.isBlank(title)) {
            throw new VcaException("标题不能为空");
        }
        if (CollUtil.isEmpty(voList)) {
            throw new VcaException("数据列表不能为空");
        }
        if (CollUtil.isEmpty(aliasMap)) {
            throw new VcaException("别名map不能为空");
        }

        // 文件名部分
        String newFileName = UploadUtil.getWebPath() + fileName;
        String filePath = UploadUtil.getServerPath();

        // 判断是否存在当前目录，不存在则创建
        File file = new File(filePath);
        if (!file.exists()) {
            file.mkdirs();
        }
        fileName = filePath.concat(fileName);

        // 通过工具类创建writer
        ExcelWriter writer = ExcelUtil.getWriter(fileName);
//        ExcelWriter writer = ExcelUtil.getWriter("d:/writeMapTest.xlsx");
        CellStyle headCellStyle = writer.getHeadCellStyle();
        Font font = writer.createFont();
        font.setBold(true);
        headCellStyle.setFont(font);

        //自定义标题别名
        aliasMap.forEach((key, value) -> writer.addHeaderAlias(key, value));
        // 合并单元格后的标题行，使用默认标题样式
        writer.merge(aliasMap.size() - 1, title);
        writer.merge(aliasMap.size() - 1, StrUtil.format("生成时间:{}", DateUtil.nowDateTimeStr()));
        //设置宽度自适应
        writer.setColumnWidth(-1, 22);
        // 一次性写出内容，使用默认样式，强制输出标题
        writer.write(voList, true);
        // 关闭writer，释放内存
        writer.close();

        return newFileName;
    }

    /**
     * 上传部分设置
     */
    public static void setUpload(String rootPath, String modelPath, String type) {
        if (StrUtil.isBlank(rootPath) || StrUtil.isBlank(modelPath) || StrUtil.isBlank(type)) {
            throw new VcaException("请检查上传参数，上传参数不能为空");
        }
        UploadUtil.setRootPath(rootPath);
        UploadUtil.setModelPath("file" + "/" + modelPath);
//        UploadUtil.setType(type);
    }

    /**
     * 课程排期导出
     *
     * @param voList
     * @param year
     * @param month
     * @return
     * @throws ParseException
     */
    @SneakyThrows
    public static XSSFWorkbook exportExeclByCourser(List<CourseSchedulingExport> voList, Integer year, Integer month) {


        //创建Excel文件
        XSSFWorkbook wb = new XSSFWorkbook();
        //生成sheet
        Sheet sheet = wb.createSheet("CourseSchedulingExport");


        XSSFCellStyle style = wb.createCellStyle();
        //设置字体样式
        Font boldFont = wb.createFont();
        //设置文字类型
        boldFont.setFontName("宋体");
        //设置加粗
        boldFont.setBold(true);
        //设置文字大小
        boldFont.setFontHeightInPoints((short) 16);
        boldFont.setColor(IndexedColors.WHITE.getIndex());
        //应用设置的字体
        style.setFont(boldFont);
        //设置边框下、左、右、上
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        //水平居中
        style.setAlignment(HorizontalAlignment.CENTER);
        //垂直居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        //自定义颜色对象
        XSSFColor colorOne1 = new XSSFColor();
        //根据你需要的rgb值获取byte数组
        colorOne1.setRGB(intToByteArray(getIntFromColor(0, 0, 0)));
        //前景填充色
        style.setFillForegroundColor(colorOne1);
        //设置前景填充样式
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        //
        sheet.createRow(0).createCell(0).setCellValue(year);
        sheet.getRow(0).getCell(0).setCellStyle(style);
        sheet.getRow(0).setHeightInPoints(30);
        //根据年月获取天数
        int day = getDayOfMonth(year, month);
        //创建 MONTH DATE COURSE CATEGORY
        ArrayList<String> list = new ArrayList<>();
        list.add("MONTH");
        list.add("DATE");
        list.add("COURSE CATEGORY");
        for (int i = 0; i < 3; i++) {
            Row row = sheet.createRow(i + 1);
            if (i == 0) {
                row.createCell(0).setCellValue(list.get(i));
                row.createCell(1).setCellValue(month + "月");
            } else {
                Cell cell = row.createCell(0);
                cell.setCellValue(list.get(i));
            }
        }

        List<String> temporalInterval = getTemporalInterval();
        //创建时间y
        //时间范围样式
        XSSFCellStyle timeCellStyle = wb.createCellStyle();
        //设置边框下、左、右、上
        timeCellStyle.setBorderBottom(BorderStyle.THIN);
        timeCellStyle.setBorderLeft(BorderStyle.THIN);
        timeCellStyle.setBorderRight(BorderStyle.THIN);
        timeCellStyle.setBorderTop(BorderStyle.THIN);
        //水平居中
        timeCellStyle.setAlignment(HorizontalAlignment.CENTER);
        //垂直居中
        timeCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //自定义颜色对象
        XSSFColor color = new XSSFColor();
        //根据你需要的rgb值获取byte数组
        color.setRGB(intToByteArray(getIntFromColor(214, 220, 228)));
        //前景填充色
        timeCellStyle.setFillForegroundColor(color);
        //设置前景填充样式
        timeCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        int c = 4;
        for (int i = 0; i < temporalInterval.size(); i++) {
            sheet.createRow(c);
            sheet.getRow(c).setHeightInPoints(30);
            sheet.getRow(c).createCell(0).setCellValue(temporalInterval.get(i));
            sheet.getRow(c).getCell(0).setCellStyle(timeCellStyle);
            c++;
        }


        //去重后的排期时间
        List<String> schedulingDates = voList.stream().map(CourseSchedulingExport::getSchedulingDate).distinct().collect(Collectors.toList());
        //去重后的分类名
        List<String> courseTypeNames = voList.stream().map(CourseSchedulingExport::getCourseTypeName).distinct().collect(Collectors.toList());

        ArrayList<String> date = new ArrayList<>();
        for (String schedulingDate : schedulingDates) {
            String substring = schedulingDate.substring(schedulingDate.indexOf("-") + 4, schedulingDate.length());
            date.add(substring);
        }

        XSSFCellStyle cellStyle1 = wb.createCellStyle();
        //设置边框下、左、右、上
        cellStyle1.setBorderBottom(BorderStyle.THIN);
        cellStyle1.setBorderLeft(BorderStyle.THIN);
        cellStyle1.setBorderRight(BorderStyle.THIN);
        cellStyle1.setBorderTop(BorderStyle.THIN);
        int d = 1;
        int lastRowNum = sheet.getLastRowNum();
        //创建日期和分类
        for (int i = 1; i <= day; i++) {
            String contains = "";
            if (i < 10) {
                contains = "0" + i;
            } else {
                contains = "" + i + "";
            }
            //判断该日期是否包含排期的日期
            if (date.contains(contains)) {
                String week = dateToWeek(schedulingDates.get(0).substring(0, 8) + contains);
                sheet.getRow(2).createCell(d).setCellValue(contains + " " + week);
                for (int j = 0; j < courseTypeNames.size(); j++) {
                    sheet.getRow(2).createCell(d + j + 1).setCellStyle(cellStyle1);
                }
                if (courseTypeNames.size() >1){
                    //合并单元格，横向
                    sheet.addMergedRegionUnsafe(new CellRangeAddress(2, 2, d, d + courseTypeNames.size() - 1));
                }

                CellStyle cs_header = wb.createCellStyle();
                //设置边框下、左、右、上
                cs_header.setBorderBottom(BorderStyle.THIN);
                cs_header.setBorderLeft(BorderStyle.THIN);
                cs_header.setBorderRight(BorderStyle.THIN);
                cs_header.setBorderTop(BorderStyle.THIN);
                //水平居中
                cs_header.setAlignment(HorizontalAlignment.CENTER);
                //垂直居中
                cs_header.setVerticalAlignment(VerticalAlignment.CENTER);
                sheet.getRow(2).getCell(d).setCellStyle(cs_header);


                CellStyle cs_header8 = wb.createCellStyle();
                //设置边框下、左、右、上

                cs_header8.setBorderLeft(BorderStyle.THIN);
                cs_header8.setBorderRight(BorderStyle.THIN);

                CellStyle cs_header1 = wb.createCellStyle();
                //设置边框下、左、右、上
                cs_header1.setBorderBottom(BorderStyle.THIN);
                cs_header1.setBorderLeft(BorderStyle.THIN);
                cs_header1.setBorderRight(BorderStyle.THIN);
                //循环赋值分类名称
                for (int j = 0; j < courseTypeNames.size(); j++) {

                    for (int k = 3; k <= lastRowNum; k++) {
                        if (k == lastRowNum) {
                            sheet.getRow(k).createCell(d + j).setCellStyle(cs_header1);
                        } else {
                            sheet.getRow(k).createCell(d + j).setCellStyle(cs_header8);
                        }

                    }

                    sheet.getRow(3).getCell(d + j).setCellValue(courseTypeNames.get(j));
                    sheet.getRow(3).getCell(d + j).setCellStyle(timeCellStyle);
                }

                d = d + courseTypeNames.size();

            } else {
                String week = dateToWeek(schedulingDates.get(0).substring(0, 8) + contains);
                CellStyle cs_header7 = wb.createCellStyle();
                //设置边框下、左、右、上
                cs_header7.setBorderBottom(BorderStyle.THIN);
                cs_header7.setBorderLeft(BorderStyle.THIN);
                cs_header7.setBorderRight(BorderStyle.THIN);
                cs_header7.setBorderTop(BorderStyle.THIN);
                //前景填充色
                cs_header7.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index);
                //设置前景填充样式
                cs_header7.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                //水平居中
                cs_header7.setAlignment(HorizontalAlignment.CENTER);
                //垂直居中
                cs_header7.setVerticalAlignment(VerticalAlignment.CENTER);
                Cell cell5 = sheet.getRow(2).createCell(d);
                cell5.setCellValue(contains + " " + week);
                cell5.setCellStyle(cs_header7);

                for (int j = 3; j <= lastRowNum; j++) {
                    sheet.getRow(j).createCell(d).setCellStyle(cs_header7);
                }
                sheet.addMergedRegionUnsafe(new CellRangeAddress(4, lastRowNum, d, d));

                d++;
            }

        }

        //合并月份单元格 设置月份的样式
        sheet.addMergedRegionUnsafe(new CellRangeAddress(1, 1, 1, sheet.getRow(2).getLastCellNum() - 1));
        XSSFCellStyle cs_header = wb.createCellStyle();
        //设置边框下、左、右、上
//                cs_header.setBorderBottom(BorderStyle.THIN);
//                cs_header.setBorderLeft(BorderStyle.THIN);
//                cs_header.setBorderRight(BorderStyle.THIN);
//                cs_header.setBorderTop(BorderStyle.THIN);
        //水平居中
        cs_header.setAlignment(HorizontalAlignment.CENTER);
        //垂直居中
        cs_header.setVerticalAlignment(VerticalAlignment.CENTER);
        //自定义颜色对象
        XSSFColor colorOne = new XSSFColor();
        //根据你需要的rgb值获取byte数组
        colorOne.setRGB(intToByteArray(getIntFromColor(214, 220, 228)));
        //前景填充色
        cs_header.setFillForegroundColor(colorOne);
        //设置前景填充样式
        cs_header.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        sheet.getRow(1).getCell(1).setCellStyle(cs_header);


        sheet.setColumnWidth(0, 4000);//设置第一列的宽度是35宽度
        //隐藏网格线
        sheet.setDisplayGridlines(false);

//        sheet.addMergedRegionUnsafe(new CellRangeAddress(4, 5, 3, 3));

        //创建排期信息
        for (CourseSchedulingExport courserSchedulingExport : voList) {

            //获取所有的行
            int sum = sheet.getLastRowNum();

            //合并单元格的起始行
            int startRow = 0;
            //合并单元格的结束行
            int endRow = 0;
            //合并单元格的起始列
            int startCell = 0;
            //合并单元格的结束列
            int endCell = 0;
            //循环判断获取开始行和结束行
            for (int i = 4; i <= sum; ++i) {
                String eachTime = sheet.getRow(i).getCell(0).getStringCellValue();
                if (getStartIsTimeInclude(courserSchedulingExport.getSchedulingStartTime(), eachTime)) {
                    startRow = i;
                }
                if (getEndIsTimeInclude(courserSchedulingExport.getSchedulingEndTime(), eachTime)) {
                    endRow = i;
                }
            }

            //循环判断获取开始列和结束列
            //获取指定行的所有列
            short cellNum = sheet.getRow(2).getLastCellNum();
            for (int i = 1; i < cellNum; i++) {
                //指定单元格数据
                if (ObjectUtil.isEmpty(sheet.getRow(2).getCell(i).getStringCellValue())) {
                    continue;
                }
                String stringCellValue = sheet.getRow(2).getCell(i).getStringCellValue();
                //排期日期
                String schedulingDate = courserSchedulingExport.getSchedulingDate();
                //排期的天数
                String schedulingDay = schedulingDate.substring(schedulingDate.indexOf("-") + 4, schedulingDate.length());
                //获取对应的某一天
                if (schedulingDay.equals(stringCellValue.substring(0, 2))) {
                    startCell = i;
                    //todo j = 分类的数量
                    for (int j = 0; j < courseTypeNames.size(); j++) {
                        //分类的值
                        if (ObjectUtil.isEmpty(sheet.getRow(3).getCell(startCell + j))) {
                            continue;
                        }
                        String cellValue = sheet.getRow(3).getCell(startCell + j).getStringCellValue();
                        if (cellValue.equals(courserSchedulingExport.getCourseTypeName())) {
                            XSSFCellStyle cellStyle = wb.createCellStyle();
//                            //设置字体样式
//                            Font boldFont = wb.createFont();
//                            //设置文字类型
//                            boldFont.setFontName("宋体");
//                            //设置加粗
//                            boldFont.setColor();
//                            //设置文字大小
//                            boldFont.setFontHeightInPoints((short) 16);
                            //设置边框下、左、右、上
                            cellStyle.setBorderBottom(BorderStyle.THIN);
                            cellStyle.setBorderLeft(BorderStyle.THIN);
                            cellStyle.setBorderRight(BorderStyle.THIN);
                            cellStyle.setBorderTop(BorderStyle.THIN);
                            XSSFColor xssfColor = new XSSFColor();
                            int[] rgb = hex2RGB(courserSchedulingExport.getCourseTypeLabelColor());
                            if (ObjectUtil.isNotEmpty(rgb)) {
                                xssfColor.setRGB(intToByteArray(getIntFromColor(rgb[0], rgb[1], rgb[2])));
                                //前景填充色
                                cellStyle.setFillForegroundColor(xssfColor);
                            }

                            //设置前景填充样式
                            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                            //水平居中
                            cellStyle.setAlignment(HorizontalAlignment.CENTER);
                            //垂直居中
                            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                            cellStyle.setWrapText(true);
                            sheet.getRow(startRow).getCell(startCell + j).setCellValue(courserSchedulingExport.getCourseName());
                            sheet.getRow(startRow).getCell(startCell + j).setCellStyle(cellStyle);
                            sheet.addMergedRegionUnsafe(new CellRangeAddress(startRow, endRow, startCell + j, startCell + j));

                        }
                    }
                }
            }
        }
        return wb;
    }

    public static XSSFWorkbook exportExcelCourseSchedulingById(String[] header1, String[] header2, Boolean status, ExcelCourseSchedulingById excelCourseById) {
        //创建Excel文件
        XSSFWorkbook wb = new XSSFWorkbook();
        //生成sheet
//        String sheetName = status?"课程名单（已发生）":"课程名单（未发生）";
        String sheetName = "课程名单";
        Sheet sheet = wb.createSheet(sheetName);

        XSSFCellStyle style = wb.createCellStyle();
        //设置字体样式
        Font boldFont = wb.createFont();
        //设置文字类型
        boldFont.setFontName("等线");
        //设置加粗
//        boldFont.setBold(true);
        //设置文字大小
        boldFont.setFontHeightInPoints((short) 12);
        boldFont.setColor(IndexedColors.BLACK.getIndex());
        //应用设置的字体
        style.setFont(boldFont);
        //水平居中
        style.setAlignment(HorizontalAlignment.CENTER);
        //垂直居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        //自定义颜色对象：淡绿色
        XSSFColor colorOne1 = new XSSFColor(new java.awt.Color(226, 239, 218));
        // 设置单元格的背景颜色
        style.setFillForegroundColor(colorOne1);
        //前景填充色
        style.setFillForegroundColor(colorOne1);
        //设置前景填充样式
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        /**
         * 首行标题设置
         */
        Row headerRow1 = sheet.createRow(0);
        headerRow1.setHeightInPoints(50); //设置行高50
        for (int i = 0; i < header1.length; i++) {
            Cell cell = headerRow1.createCell(i);
            cell.setCellValue(header1[i]);
            cell.setCellStyle(style);
            sheet.setColumnWidth(i, header1[i].length()*2*256+15*256); //设置行长度
        }
        /**
         * 设定标题内容
         */
        XSSFCellStyle valueStyle = wb.createCellStyle();
        //垂直居中
        valueStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        valueStyle.setFont(boldFont);
        Row headerRow1Value = sheet.createRow(1);
        headerRow1Value.setHeightInPoints(50); //设置行高50
        for (int i = 0; i < header1.length; i++) {
            Cell cell = headerRow1Value.createCell(i);
            cell.setCellStyle(valueStyle);
            switch (i) {
                case 0:
                    cell.setCellValue(excelCourseById.getName());
                    break;
                case 1:
                    cell.setCellValue(excelCourseById.getCourseLanguage());
                    break;
                case 2:
                    cell.setCellValue(excelCourseById.getSchedulingDate());
                    break;
                case 3:
                    cell.setCellValue(excelCourseById.getSchedulingStartTime()+"-"+excelCourseById.getSchedulingEndTime());
                    break;
                case 4:
                    cell.setCellValue(excelCourseById.getStatus());
                    break;
                case 5:
                    cell.setCellValue(excelCourseById.getPrice().longValue());
                    break;
                case 6:
                    cell.setCellValue(excelCourseById.getSeatCount());
                    break;
                case 7:
                    cell.setCellValue(excelCourseById.getExternalLimit());
                    break;
                case 8:
                    cell.setCellValue(excelCourseById.getExternalAppointment());
                    break;
                case 9:
                    cell.setCellValue(excelCourseById.getExternalStudentsNumber());
                    break;
                case 10:
                    cell.setCellValue(excelCourseById.getInternalLimit());
                    break;
                case 11:
                    cell.setCellValue(excelCourseById.getInternalAppointment());
                    break;
                case 12:
                    cell.setCellValue(excelCourseById.getInternalStudentsNumber());
                    break;
                case 13:
                    cell.setCellValue(excelCourseById.getAllStudentsNumber());
                    break;
                default:break;
            }
        }

        /**
         * 填充内容标题
         */
        Row headerRow2 = sheet.createRow(2);
        headerRow2.setHeightInPoints(50); //设置行高50
        for (int i = 0; i < header2.length; i++) {
            Cell cell = headerRow2.createCell(i);
            cell.setCellValue(header2[i]);
            cell.setCellStyle(style);
        }
        /**
         * 填充内容
         */
        XSSFCellStyle serialStyle = wb.createCellStyle();
        //垂直居中
        serialStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //水平居中
        serialStyle.setAlignment(HorizontalAlignment.CENTER);
        serialStyle.setFont(boldFont);
        List<ExcelCourseOrderInfo> list = excelCourseById.getList();
        for (int i = 0; i < list.size(); i++) {
            Row valueRow = sheet.createRow(3+i);
            valueRow.setHeightInPoints(50); //设置行高50
            ExcelCourseOrderInfo info = list.get(i);
            for (int j = 0; j < header2.length; j++) {
                Cell cell = valueRow.createCell(j);
                cell.setCellStyle(valueStyle);
                switch (j) {
                    case 0:
                        cell.setCellValue(i+1);
                        cell.setCellStyle(serialStyle); //只有序号需要水平居中
                        break;
                    case 1:
                        cell.setCellValue(info.getLecturerName());
                        break;
                    case 2:
                        cell.setCellValue(info.getLecturerPhone());
                        break;
                    case 3:
                        cell.setCellValue(info.getNickname());
                        break;
                    case 4:
                        cell.setCellValue(info.getPhone());
                        break;
                    case 5:
                        cell.setCellValue(info.getEmail());
                        break;
                    case 6:
                        cell.setCellValue(info.getMerOrderNo());
                        break;
                    case 7:
                        cell.setCellValue(info.getChannel());
                        break;
                    case 8:
                        cell.setCellValue(info.getWriteOffStatus());
                        break;
                    case 9:
                        cell.setCellValue(info.getWriteOffTime());
                        break;
                    default:break;
                }
            }
        }
        return wb;
    }

    public static XSSFWorkbook exportExcelTalkSchedulingById(String[] header1, String[] header2, Boolean status, ExcelTalkSchedulingById excelTalkById) {
        //创建Excel文件
        XSSFWorkbook wb = new XSSFWorkbook();
        //生成sheet
//        String sheetName = status?"讲座名单（已发生）":"讲座名单（未发生）";
        String sheetName = "讲座名单";
        Sheet sheet = wb.createSheet(sheetName);

        XSSFCellStyle style = wb.createCellStyle();
        //设置字体样式
        Font boldFont = wb.createFont();
        //设置文字类型
        boldFont.setFontName("等线");
        //设置加粗
//        boldFont.setBold(true);
        //设置文字大小
        boldFont.setFontHeightInPoints((short) 12);
        boldFont.setColor(IndexedColors.BLACK.getIndex());
        //应用设置的字体
        style.setFont(boldFont);
        //水平居中
        style.setAlignment(HorizontalAlignment.CENTER);
        //垂直居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        //自定义颜色对象：淡绿色
        XSSFColor colorOne1 = new XSSFColor(new java.awt.Color(226, 239, 218));
        // 设置单元格的背景颜色
        style.setFillForegroundColor(colorOne1);
        //前景填充色
        style.setFillForegroundColor(colorOne1);
        //设置前景填充样式
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        /**
         * 首行标题设置
         */
        Row headerRow1 = sheet.createRow(0);
        headerRow1.setHeightInPoints(50); //设置行高50
        for (int i = 0; i < header1.length; i++) {
            Cell cell = headerRow1.createCell(i);
            cell.setCellValue(header1[i]);
            cell.setCellStyle(style);
            sheet.setColumnWidth(i, header1[i].length()*2*256+15*256); //设置行长度
        }
        /**
         * 设定标题内容
         */
        XSSFCellStyle valueStyle = wb.createCellStyle();
        //垂直居中
        valueStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        valueStyle.setFont(boldFont);
        Row headerRow1Value = sheet.createRow(1);
        headerRow1Value.setHeightInPoints(50); //设置行高50
        for (int i = 0; i < header1.length; i++) {
            Cell cell = headerRow1Value.createCell(i);
            cell.setCellStyle(valueStyle);
            switch (i) {
                case 0:
                    cell.setCellValue(excelTalkById.getTalkType());
                    break;
                case 1:
                    cell.setCellValue(excelTalkById.getName());
                    break;
                case 2:
                    cell.setCellValue(excelTalkById.getCourseLanguage());
                    break;
                case 3:
                    cell.setCellValue(excelTalkById.getSchedulingDate());
                    break;
                case 4:
                    cell.setCellValue(excelTalkById.getSchedulingStartTime()+"-"+excelTalkById.getSchedulingEndTime());
                    break;
                case 5:
                    cell.setCellValue(excelTalkById.getStatus());
                    break;
                case 6:
                    cell.setCellValue(excelTalkById.getPrice().longValue());
                    break;
                case 7:
                    cell.setCellValue(excelTalkById.getQuota());
                    break;
                case 8:
                    cell.setCellValue(excelTalkById.getNumberOfAppointments());
                    break;
                case 9:
                    cell.setCellValue(excelTalkById.getNumberOfParticipants());
                    break;
                default:break;
            }
        }

        /**
         * 填充内容标题
         */
        Row headerRow2 = sheet.createRow(2);
        headerRow2.setHeightInPoints(50); //设置行高50
        for (int i = 0; i < header2.length; i++) {
            Cell cell = headerRow2.createCell(i);
            cell.setCellValue(header2[i]);
            cell.setCellStyle(style);
        }
        /**
         * 填充内容
         */
        XSSFCellStyle serialStyle = wb.createCellStyle();
        //垂直居中
        serialStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //水平居中
        serialStyle.setAlignment(HorizontalAlignment.CENTER);
        serialStyle.setFont(boldFont);
        List<ExcelTalkOrderInfo> list = excelTalkById.getList();
        for (int i = 0; i < list.size(); i++) {
            Row valueRow = sheet.createRow(3+i);
            valueRow.setHeightInPoints(50); //设置行高50
            ExcelTalkOrderInfo info = list.get(i);
            for (int j = 0; j < header2.length; j++) {
                Cell cell = valueRow.createCell(j);
                cell.setCellStyle(valueStyle);
                switch (j) {
                    case 0:
                        cell.setCellValue(i+1);
                        cell.setCellStyle(serialStyle); //只有序号需要水平居中
                        break;
                    case 1:
                        cell.setCellValue(info.getLecturerName());
                        break;
                    case 2:
                        cell.setCellValue(info.getLecturerPhone());
                        break;
                    case 3:
                        cell.setCellValue(info.getNickname());
                        break;
                    case 4:
                        cell.setCellValue(info.getPhone());
                        break;
                    case 5:
                        cell.setCellValue(info.getEmail());
                        break;
                    case 6:
                        cell.setCellValue(info.getMerOrderNo());
                        break;
                    case 7:
                        cell.setCellValue(info.getChannel());
                        break;
                    case 8:
                        cell.setCellValue(info.getWriteOffStatus());
                        break;
                    case 9:
                        cell.setCellValue(info.getWriteOffTime());
                        break;
                    default:break;
                }
            }
        }
        return wb;
    }

    public static XSSFWorkbook exportExcelExSchedulingById(String[] header1, String[] header2, Boolean status, ExcelExSchedulingById excelExById) {
        //创建Excel文件
        XSSFWorkbook wb = new XSSFWorkbook();
        //生成sheet
//        String sheetName = status?"展览名单（已发生）":"展览名单（未发生）";
        String sheetName = "展览名单";
        Sheet sheet = wb.createSheet(sheetName);

        XSSFCellStyle style = wb.createCellStyle();
        //设置字体样式
        Font boldFont = wb.createFont();
        //设置文字类型
        boldFont.setFontName("等线");
        //设置加粗
//        boldFont.setBold(true);
        //设置文字大小
        boldFont.setFontHeightInPoints((short) 12);
        boldFont.setColor(IndexedColors.BLACK.getIndex());
        //应用设置的字体
        style.setFont(boldFont);
        //水平居中
        style.setAlignment(HorizontalAlignment.CENTER);
        //垂直居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        //自定义颜色对象：淡绿色
        XSSFColor colorOne1 = new XSSFColor(new java.awt.Color(226, 239, 218));
        // 设置单元格的背景颜色
        style.setFillForegroundColor(colorOne1);
        //前景填充色
        style.setFillForegroundColor(colorOne1);
        //设置前景填充样式
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        /**
         * 首行标题设置
         */
        Row headerRow1 = sheet.createRow(0);
        headerRow1.setHeightInPoints(50); //设置行高50
        for (int i = 0; i < header1.length; i++) {
            Cell cell = headerRow1.createCell(i);
            cell.setCellValue(header1[i]);
            cell.setCellStyle(style);
            sheet.setColumnWidth(i, header1[i].length()*2*256+15*256); //设置行长度
        }
        /**
         * 设定标题内容
         */
        XSSFCellStyle valueStyle = wb.createCellStyle();
        //垂直居中
        valueStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        valueStyle.setFont(boldFont);
        Row headerRow1Value = sheet.createRow(1);
        headerRow1Value.setHeightInPoints(50); //设置行高50
        for (int i = 0; i < header1.length; i++) {
            Cell cell = headerRow1Value.createCell(i);
            cell.setCellStyle(valueStyle);
            switch (i) {
                case 0:
                    cell.setCellValue(excelExById.getName());
                    break;
                case 1:
                    cell.setCellValue(excelExById.getSchedulingDate());
                    break;
                case 2:
                    cell.setCellValue(excelExById.getSchedulingStartTime()+"-"+excelExById.getSchedulingEndTime());
                    break;
                case 3:
                    cell.setCellValue(excelExById.getStatus());
                    break;
                case 4:
                    cell.setCellValue(excelExById.getQuota());
                    break;
                case 5:
                    cell.setCellValue(excelExById.getNumberOfAppointments());
                    break;
                case 6:
                    cell.setCellValue(excelExById.getNumberOfParticipants());
                    break;
                default:break;
            }
        }

        /**
         * 填充内容标题
         */
        Row headerRow2 = sheet.createRow(2);
        headerRow2.setHeightInPoints(50); //设置行高50
        for (int i = 0; i < header2.length; i++) {
            Cell cell = headerRow2.createCell(i);
            cell.setCellValue(header2[i]);
            cell.setCellStyle(style);
            if(i>=header1.length) {
                sheet.setColumnWidth(i, header2[i].length()*2*256+15*256); //设置行长度
            }
        }
        /**
         * 填充内容
         */
        XSSFCellStyle serialStyle = wb.createCellStyle();
        //垂直居中
        serialStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //水平居中
        serialStyle.setAlignment(HorizontalAlignment.CENTER);
        serialStyle.setFont(boldFont);
        List<ExcelExOrderInfo> list = excelExById.getList();
        for (int i = 0; i < list.size(); i++) {
            Row valueRow = sheet.createRow(3+i);
            valueRow.setHeightInPoints(50); //设置行高50
            ExcelExOrderInfo info = list.get(i);
            for (int j = 0; j < header2.length; j++) {
                Cell cell = valueRow.createCell(j);
                cell.setCellStyle(valueStyle);
                switch (j) {
                    case 0:
                        cell.setCellValue(i+1);
                        cell.setCellStyle(serialStyle); //只有序号需要水平居中
                        break;
                    case 1:
                        cell.setCellValue(info.getLecturerName());
                        break;
                    case 2:
                        cell.setCellValue(info.getLecturerPhone());
                        break;
                    case 3:
                        cell.setCellValue(info.getNickname());
                        break;
                    case 4:
                        cell.setCellValue(info.getPhone());
                        break;
                    case 5:
                        cell.setCellValue(info.getEmail());
                        break;
                    case 6:
                        cell.setCellValue(info.getMerOrderNo());
                        break;
                    case 7:
                        cell.setCellValue(info.getChannel());
                        break;
                    case 8:
                        cell.setCellValue(info.getWriteOffStatus());
                        break;
                    case 9:
                        cell.setCellValue(info.getWriteOffTime());
                        break;
                    default:break;
                }
            }
        }
        return wb;
    }
    /**
     * 根据年月日获取周几（英文）
     *
     * @param datetime
     * @return
     */
    public static String dateToWeek(String datetime) {
        SimpleDateFormat f = new SimpleDateFormat("yyyy-MM-dd");
        String[] weekDays = {"SUN", "MON", "TUE", "WED", "THU", "FRI", "SAT"};
        Calendar cal = Calendar.getInstance(); // 获得一个日历
        Date datet = null;
        try {
            datet = f.parse(datetime);
        } catch (ParseException e) {
            throw new RuntimeException(e);
        }
        cal.setTime(datet);
        int w = cal.get(Calendar.DAY_OF_WEEK) - 1; // 指示一个星期中的某天。
        if (w < 0)
            w = 0;
        return weekDays[w];
    }

    /**
     * 获取某年某月有多少天
     *
     * @param year  年
     * @param month 月
     * @return 某年某月有多少天
     */
    public static int getDayOfMonth(int year, int month) {
        Calendar c = Calendar.getInstance();
        c.set(year, month, 0);
        return c.get(Calendar.DAY_OF_MONTH);
    }


    /**
     * 比较指定开始时间是否在时间取件之内
     */
    public static Boolean getStartIsTimeInclude(String startTime, String timeInclude) throws ParseException {
        ArrayList<String> list = new ArrayList<>();
        for (String s : timeInclude.split("-")) {
            list.add(s);
        }

        SimpleDateFormat sdf = new SimpleDateFormat("HH:mm");
        //区间开始时间
        Long IncludeStartTime = sdf.parse(list.get(0)).getTime();
        //区间结束时间
        Long IncludeEndTime = sdf.parse(list.get(1)).getTime();
        //开始时间
        Long startT = sdf.parse(startTime).getTime();
        //当前时间大于开始时间 小于结束时间
        if (startT >= IncludeStartTime && startT < IncludeEndTime) {
            return true;
        } else {
            return false;
        }
    }


    /**
     * 比较指定结束时间是否在时间取件之内
     */
    public static Boolean getEndIsTimeInclude(String endTime, String timeInclude) throws ParseException {
        ArrayList<String> list = new ArrayList<>();
        for (String s : timeInclude.split("-")) {
            list.add(s);
        }

        SimpleDateFormat sdf = new SimpleDateFormat("HH:mm");
        //区间开始时间
        Long IncludeStartTime = sdf.parse(list.get(0)).getTime();
        //区间结束时间
        Long IncludeEndTime = sdf.parse(list.get(1)).getTime();
        //结束时间
        Long endT = sdf.parse(endTime).getTime();
        //当前时间大于开始时间 小于结束时间
        if (endT > IncludeStartTime && endT <= IncludeEndTime) {
            return true;
        } else {
            return false;
        }
    }

    public static int[] hex2RGB(String hexStr) {
        if (hexStr != null && !"".equals(hexStr) && hexStr.length() == 7) {
            int[] rgb = new int[3];
            rgb[0] = Integer.valueOf(hexStr.substring(1, 3), 16);
            rgb[1] = Integer.valueOf(hexStr.substring(3, 5), 16);
            rgb[2] = Integer.valueOf(hexStr.substring(5, 7), 16);
            return rgb;
        }
        return null;
    }


    /**
     * rgb转int
     */
    private static int getIntFromColor(int Red, int Green, int Blue) {
        Red = (Red << 16) & 0x00FF0000;
        Green = (Green << 8) & 0x0000FF00;
        Blue = Blue & 0x000000FF;
        return 0xFF000000 | Red | Green | Blue;
    }

    /**
     * int转byte[]
     */
    public static byte[] intToByteArray(int i) {
        byte[] result = new byte[4];
        result[0] = (byte) ((i >> 24) & 0xFF);
        result[1] = (byte) ((i >> 16) & 0xFF);
        result[2] = (byte) ((i >> 8) & 0xFF);
        result[3] = (byte) (i & 0xFF);
        return result;
    }


    public static List<String> getTemporalInterval() {
        ArrayList<String> list = new ArrayList<>();
        list.add("09:00-09:30");
        list.add("09:30-10:00");
        list.add("10:00-10:30");
        list.add("10:30-11:00");
        list.add("11:00-11:30");
        list.add("11:30-12:00");
        list.add("12:00-12:30");
        list.add("12:30-13:00");
        list.add("13:00-13:30");
        list.add("13:30-14:00");
        list.add("14:00-14:30");
        list.add("14:30-15:30");
        list.add("15:00-15:30");
        list.add("15:30-16:00");
        list.add("16:00-16:30");
        list.add("16:30-17:30");
        list.add("17:00-17:30");
        list.add("17:30-18:00");
        list.add("18:00-18:30");
        list.add("18:30-19:00");
        list.add("19:00-19:30");
        list.add("19:30-20:00");
        list.add("20:00-20:30");
        list.add("20:30-21:00");
        return list;
    }


//    public static List<String> getDateList(String startTime,String endTime){
//
//        String start = "";
//        String substring = startTime.substring(3, 5);
//        if (Integer.parseInt(substring)>=30){
//            start = startTime.substring(0,3)+"30"+"-"+Integer.parseInt(startTime.substring(0,2))+1+":00";
//        }else {
//            start = startTime.substring(0,3)+"00"+"-"+startTime.substring(0,3)+":30";
//        }
//
//        return null;
//
//    }


    /**
     * @param title    标题
     * @param headers  表头
     * @param values   表中元素
     * @param fileName 文件名称
     * @return
     */
    @SneakyThrows
    public static String detailClient(String title, List<String> headers, List<List<String>> values, Map<String, List<String>> child, String fileName,Map<Integer,String> subscript) {

        //创建一个HSSFWorkbook，对应一个Excel文件
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook();

        //在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet hssfSheet = hssfWorkbook.createSheet(title);

        //设置单元格颜色
        HSSFPalette customPalette = hssfWorkbook.getCustomPalette();
        //根据你需要的rgb值获取byte数组
        customPalette.setColorAtIndex(IndexedColors.GREY_25_PERCENT.index, (byte) 217, (byte) 225, (byte) 242);
        customPalette.setColorAtIndex(IndexedColors.GREY_50_PERCENT.index, (byte) 255, (byte) 241, (byte) 203);

        //设置值表头样式 设置表头居中
        HSSFCellStyle hssfCellStyle = hssfWorkbook.createCellStyle();
        //垂直居中
        hssfCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //水平居中
        hssfCellStyle.setAlignment(HorizontalAlignment.CENTER);
        hssfCellStyle.setBorderBottom(BorderStyle.THIN);
        hssfCellStyle.setBorderLeft(BorderStyle.THIN);
        hssfCellStyle.setBorderRight(BorderStyle.THIN);
        hssfCellStyle.setBorderTop(BorderStyle.THIN);
        hssfCellStyle.setWrapText(true);
        hssfCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index);
        //设置前景填充样式
        hssfCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        HSSFFont font = hssfWorkbook.createFont();
        font.setFontName("等线");
        font.setFontHeightInPoints((short) 11);//设置字体大小
        hssfCellStyle.setFont(font);//选择需要用到的字体格式

        //设置值表头样式 设置表头居中
        HSSFCellStyle hssfCellStyle1 = hssfWorkbook.createCellStyle();
        //垂直居中
        hssfCellStyle1.setVerticalAlignment(VerticalAlignment.CENTER);
        //水平居中
        hssfCellStyle1.setAlignment(HorizontalAlignment.CENTER);
        hssfCellStyle1.setBorderBottom(BorderStyle.THIN);
        hssfCellStyle1.setBorderLeft(BorderStyle.THIN);
        hssfCellStyle1.setBorderRight(BorderStyle.THIN);
        hssfCellStyle1.setBorderTop(BorderStyle.THIN);
        hssfCellStyle1.setWrapText(true);
        //根据你需要的rgb值获取byte数组
        hssfCellStyle1.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.index);
        //设置前景填充样式
        hssfCellStyle1.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        hssfCellStyle1.setFont(font);//选择需要用到的字体格式

        //产生表头
        HSSFRow row = hssfSheet.createRow(0);
        row.setHeight((short) (48 * 20));
        HSSFRow row1 = hssfSheet.createRow(1);
        row1.setHeight((short) (48 * 20));
        int a = 0;
        int g = 0;
        int c = 100;
        for (String header : headers) {
            HSSFCellStyle hssfCellStyle2 = null;
            if (header.equals(headers.get(16))) {
                c = g;
            }
            if (g >= c) {
                hssfCellStyle2 = hssfCellStyle1;
            } else {
                hssfCellStyle2 = hssfCellStyle;
            }
            if (ObjectUtil.isNotEmpty(child.get(header))) {
                a = g;
                for (int j = 0; j < child.get(header).size(); j++) {
                    HSSFCell hssfCell1 = row1.createCell(a);
                    hssfCell1.setCellStyle(hssfCellStyle2);
                    hssfCell1.setCellValue(child.get(header).get(j));
                    HSSFCell hssfCell2 = row1.createCell(a + 1);
                    hssfCell2.setCellStyle(hssfCellStyle2);
                    a += 2;
                }
            } else {
                HSSFCell hssfCell1 = row1.createCell(g);
                hssfCell1.setCellStyle(hssfCellStyle2);
            }
            if (ObjectUtil.isNotEmpty(child.get(header))) {
                HSSFCell hssfCell = row.createCell(g);
                hssfCell.setCellValue(header);
                hssfCell.setCellStyle(hssfCellStyle2);
                g += child.get(header).size()>1?child.get(header).size()+1:child.get(header).size();
            } else {
                g = Math.max(g, a);
                HSSFCell hssfCell = row.createCell(g);
                hssfCell.setCellValue(header);
                hssfCell.setCellStyle(hssfCellStyle2);
            }
            g++;
        }
        for (int i = 0; i < g; i++) {
            hssfSheet.setColumnWidth(i, (int) (31.36 * 255));
        }

        int q = 0;
        for (String header : headers) {
            if (ObjectUtil.isNotEmpty(child.get(header))) {
                int i = q - 1 + child.get(header).size() * 2;
                if (CollUtil.isEmpty(isIntersection(hssfSheet, 0, 0, q, i))) {
                    hssfSheet.addMergedRegion(new CellRangeAddress(0, 0, q, i));
                }
                //创建标题合并行
                a = q;
                for (int j = 0; j < child.get(header).size(); j++) {
                    if (CollUtil.isEmpty(isIntersection(hssfSheet, 1, 1, a, a + 1))) {
                        hssfSheet.addMergedRegion(new CellRangeAddress(1, 1, a, a + 1));

                    }
                    a += 2;
                }
                q = i;
            } else {
                if (CollUtil.isEmpty(isIntersection(hssfSheet, 0, 1, q, q))) {
                    //创建标题合并
                    hssfSheet.addMergedRegion(new CellRangeAddress(0, 1, q, q));
                }
            }
            q++;
        }
        for (int i = 0; i < values.size(); i++) {
            String sub = subscript.get(i);
            if (ObjectUtil.isNotEmpty(sub)){
                List<Integer> list = VcaUtil.stringToArray(sub);
                for (int j = 0; j < 7; j++) {
                    if (CollUtil.isEmpty(isIntersection(hssfSheet, list.get(0)+2, list.get(1)+2, j, j ))) {
                        hssfSheet.addMergedRegion(new CellRangeAddress(list.get(0)+2, list.get(1)+2, j, j));
                    }
                }
                for (int j = 13; j < 16; j++) {
                    if (CollUtil.isEmpty(isIntersection(hssfSheet, list.get(0)+2, list.get(1)+2, j, j))) {
                        hssfSheet.addMergedRegion(new CellRangeAddress(list.get(0)+2, list.get(1)+2, j, j));
                    }
                }
                for (int j = a; j < headers.size()+1; j++) {
                    if (CollUtil.isEmpty(isIntersection(hssfSheet, list.get(0)+2, list.get(1)+2, j, j))) {
                        hssfSheet.addMergedRegion(new CellRangeAddress(list.get(0)+2, list.get(1)+2, j, j));
                    }
                }
            }
        }

        Map<String, HSSFCellStyle> cellStyles = getCellStyles(hssfWorkbook, font);
        //创建内容
        for (int i = 0; i < values.size(); i++) {
            row = hssfSheet.createRow(i + 2);
            row.setHeight((short) (48 * 20));
            for (int j = 0; j < values.get(i).size(); j++) {
                //将内容按顺序赋给对应列对象
                HSSFCell hssfCell = row.createCell(j);
                setNumberFormat(hssfWorkbook,cellStyles,hssfCell,values.get(i).get(j));
            }
        }
        // 文件名部分
        String newFileName = UploadUtil.getWebPath() + fileName;
        String filePath = UploadUtil.getServerPath();
        //本地测试地址
//        String newFileName = "xxx" + fileName;
//        String filePath = "xxx";

        // 判断是否存在当前目录，不存在则创建
        File file = new File(filePath);
        if (!file.exists()) {
            file.mkdirs();
        }
        fileName = filePath.concat(fileName);
        OutputStream os = new FileOutputStream(fileName);
        hssfWorkbook.write(os);
        os.flush();
        os.close();
        return newFileName;
//        return hssfWorkbook;
    }

    /**
     * @param title    标题
     * @param headers  表头
     * @param values   表中元素
     * @param fileName 文件名称
     * @return
     */
    @SneakyThrows
    public static String summaryDay(String title, List<String> headers, List<List<String>> values, Map<String, List<String>> child, String fileName) {

        //创建一个HSSFWorkbook，对应一个Excel文件
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook();

        //在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet hssfSheet = hssfWorkbook.createSheet(title);

        //设置值表头样式 设置表头居中
        HSSFCellStyle hssfCellStyle = hssfWorkbook.createCellStyle();
        //垂直居中
        hssfCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //水平居中
        hssfCellStyle.setAlignment(HorizontalAlignment.CENTER);
        hssfCellStyle.setBorderBottom(BorderStyle.THIN);
        hssfCellStyle.setBorderLeft(BorderStyle.THIN);
        hssfCellStyle.setBorderRight(BorderStyle.THIN);
        hssfCellStyle.setBorderTop(BorderStyle.THIN);
        hssfCellStyle.setWrapText(true);
        //设置单元格颜色
        HSSFPalette customPalette1 = hssfWorkbook.getCustomPalette();
        //根据你需要的rgb值获取byte数组
        customPalette1.setColorAtIndex(IndexedColors.GREY_25_PERCENT.index, (byte) 217, (byte) 225, (byte) 242);
        hssfCellStyle.setFillForegroundColor((short) 217);
        hssfCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index);
        //设置前景填充样式
        hssfCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        HSSFFont font = hssfWorkbook.createFont();
        font.setFontName("等线");
        font.setFontHeightInPoints((short) 11);//设置字体大小
        hssfCellStyle.setFont(font);//选择需要用到的字体格式

        //设置值表头样式 设置表头居中
        HSSFCellStyle cell = hssfWorkbook.createCellStyle();
        //垂直居中
        cell.setVerticalAlignment(VerticalAlignment.CENTER);
        //水平居中
        cell.setAlignment(HorizontalAlignment.CENTER);
        cell.setBorderBottom(BorderStyle.THIN);
        cell.setBorderLeft(BorderStyle.THIN);
        cell.setBorderRight(BorderStyle.THIN);
        cell.setBorderTop(BorderStyle.THIN);
        cell.setWrapText(true);
        //设置单元格颜色（颜色对应枚举会放在下面）
        cell.setFillForegroundColor(IndexedColors.YELLOW.index);
        //设置前景填充样式
        cell.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cell.setFont(font);//选择需要用到的字体格式


        int a = 0;
        int g = 0;
        //产生表头
        HSSFRow row = hssfSheet.createRow(0);
        row.setHeight((short) (48 * 20));
        HSSFRow row1 = hssfSheet.createRow(2);
        HSSFRow row2 = hssfSheet.createRow(1);
        row2.setHeight((short) (48 * 10));
        row1.setHeight((short) (48 * 10));
        for (String header : headers) {
            if (ObjectUtil.isNotEmpty(child.get(header))) {
                a = g;
                for (int j = 0; j < child.get(header).size(); j++) {
                    HSSFCell hssfCell2 = row2.createCell(a);
                    hssfCell2.setCellStyle(hssfCellStyle);
                    if (j == 0) {
                        hssfCell2.setCellValue("Total");
                    } else if ((j == 3)) {
                        hssfCell2.setCellValue(child.get(header).get(j));
                    }
                    HSSFCell hssfCell1 = row1.createCell(a);
                    hssfCell1.setCellValue(child.get(header).get(j));
                    hssfCell1.setCellStyle(cell);
                    a += 1;
                }
            }
            if (ObjectUtil.isNotEmpty(child.get(header))) {
                HSSFCell hssfCell = row.createCell(g);
                hssfCell.setCellStyle(hssfCellStyle);
                hssfCell.setCellValue(header);
                g += child.get(header).size();
            } else {
                HSSFCell hssfCell = row.createCell(g);
                hssfCell.setCellStyle(hssfCellStyle);
                hssfCell.setCellValue(header);
                g++;
            }
        }

        //设置行宽
        for (int i = 0; i < 12; i++) {
            hssfSheet.setColumnWidth(i, (int) (23.92 * 255));
        }
        //设置需要合并的表头
        hssfSheet.addMergedRegion(new CellRangeAddress(0, 2, 0, 0));
        hssfSheet.addMergedRegion(new CellRangeAddress(0, 0, 1, 4));
        hssfSheet.addMergedRegion(new CellRangeAddress(0, 1, 5, 7));
        hssfSheet.addMergedRegion(new CellRangeAddress(0, 1, 8, 11));
        hssfSheet.addMergedRegion(new CellRangeAddress(1, 1, 1, 3));
        hssfSheet.addMergedRegion(new CellRangeAddress(1, 2, 4, 4));

        Map<String, HSSFCellStyle> cellStyles = getCellStyles(hssfWorkbook, font);
        //创建内容
        for (int i = 0; i < values.size(); i++) {
            row = hssfSheet.createRow(i + 3);
            row.setHeight((short) (48 * 20));
            for (int j = 0; j < values.get(i).size(); j++) {
                //将内容按顺序赋给对应列对象
                HSSFCell hssfCell = row.createCell(j);
                setNumberFormat(hssfWorkbook,cellStyles,hssfCell,values.get(i).get(j));
            }
        }
//        return hssfWorkbook;
        String newFileName = UploadUtil.getWebPath() + fileName;
        String filePath = UploadUtil.getServerPath();
        //本地测试地址
//        String newFileName = "xxx" + fileName;
//        String filePath = "xxx";

        // 判断是否存在当前目录，不存在则创建
        File file = new File(filePath);
        if (!file.exists()) {
            file.mkdirs();
        }
        fileName = filePath.concat(fileName);
        OutputStream os = new FileOutputStream(fileName);
        hssfWorkbook.write(os);
        os.flush();
        os.close();
        return newFileName;
    }

    /**
     * @param title    标题
     * @param headers  表头
     * @param values   表中元素
     * @param fileName 文件名称
     * @return
     */
    @SneakyThrows
    public static String exportData(String title, List<String> headers, List<List<String>> values, String fileName) {

        //创建一个HSSFWorkbook，对应一个Excel文件
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook();

        //在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet hssfSheet = hssfWorkbook.createSheet(title);

        //设置值表头样式 设置表头居中
        HSSFCellStyle hssfCellStyle = hssfWorkbook.createCellStyle();
        //垂直居中
        hssfCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //水平居中
        hssfCellStyle.setAlignment(HorizontalAlignment.CENTER);
        hssfCellStyle.setBorderBottom(BorderStyle.THIN);
        hssfCellStyle.setBorderLeft(BorderStyle.THIN);
        hssfCellStyle.setBorderRight(BorderStyle.THIN);
        hssfCellStyle.setBorderTop(BorderStyle.THIN);
        hssfCellStyle.setWrapText(true);
        //设置单元格颜色（颜色对应枚举会放在下面）
        hssfCellStyle.setFillForegroundColor(IndexedColors.WHITE.index);
        //设置前景填充样式
        hssfCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        HSSFFont font = hssfWorkbook.createFont();
        font.setFontName("等线");
        font.setFontHeightInPoints((short) 11);//设置字体大小
        hssfCellStyle.setFont(font);//选择需要用到的字体格式

        //设置值表头样式 设置表头居中
        HSSFCellStyle cell = hssfWorkbook.createCellStyle();
        //垂直居中
        cell.setVerticalAlignment(VerticalAlignment.CENTER);
        //水平居中
        cell.setAlignment(HorizontalAlignment.CENTER);
        cell.setBorderBottom(BorderStyle.THIN);
        cell.setBorderLeft(BorderStyle.THIN);
        cell.setBorderRight(BorderStyle.THIN);
        cell.setBorderTop(BorderStyle.THIN);
        cell.setWrapText(true);
        //设置单元格颜色（颜色对应枚举会放在下面）
        cell.setFillForegroundColor(IndexedColors.YELLOW.index);
        //设置前景填充样式
        cell.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cell.setFont(font);//选择需要用到的字体格式

        Map<String, HSSFCellStyle> cellStyles = getCellStyles(hssfWorkbook, font);

        //产生表头
        HSSFRow row = hssfSheet.createRow(0);
        row.setHeight((short) (45 * 23));
        for (int i = 0; i < headers.size(); i++) {
            if (ObjectUtil.isNotEmpty(headers.get(i))) {
                HSSFCell hssfCell = row.createCell(i);
                hssfCell.setCellStyle(hssfCellStyle);
                hssfCell.setCellValue(headers.get(i));
                hssfSheet.setColumnWidth(i, (int) (23.92 * 255));
            }
        }

        //创建内容
        for (int i = 0; i < values.size(); i++) {
            row = hssfSheet.createRow(i+1);
            row.setHeight((short) (45 * 23));
            for (int j = 0; j < values.get(i).size(); j++) {
                //将内容按顺序赋给对应列对象
                HSSFCell hssfCell = row.createCell(j);
                setNumberFormat(hssfWorkbook,cellStyles,hssfCell,values.get(i).get(j));
            }
        }
//        return hssfWorkbook;
        String newFileName = UploadUtil.getWebPath() + fileName;
        String filePath = UploadUtil.getServerPath();
        //本地测试地址
//        String newFileName = "xxx" + fileName;
//        String filePath = "xxx";

        // 判断是否存在当前目录，不存在则创建
        File file = new File(filePath);
        if (!file.exists()) {
            file.mkdirs();
        }
        fileName = filePath.concat(fileName);
        OutputStream os = new FileOutputStream(fileName);
        hssfWorkbook.write(os);
        os.flush();
        os.close();
        return newFileName;
    }

    private static List<CellRangeAddress> isIntersection(HSSFSheet sheet, int startRowIndex, int endRowIndex, int startColIndex, int endColIndex) {
        List<CellRangeAddress> cellRangeAddressList = sheet.getMergedRegions();
        List<CellRangeAddress> haveCellRangeAddressList = CollUtil.isNotEmpty(cellRangeAddressList) ? cellRangeAddressList.stream().filter(x ->
                ScopeUtil.isIntersection(x.getFirstColumn(), x.getLastColumn(), startColIndex, endColIndex)
                        && ScopeUtil.isIntersection(x.getFirstRow(), x.getLastRow(), startRowIndex, endRowIndex)).collect(Collectors.toList()) : new ArrayList<>();
        return haveCellRangeAddressList;
    }


    /**
     *  设置Excel单元格为数值类型便于公式运算
     *
     * @param workbook
     * @param map
     * @param cell
     * @param data
     */
    public static void setNumberFormat(HSSFWorkbook workbook, Map<String,HSSFCellStyle> map, HSSFCell cell, Object data){
        boolean isNum = false;//data是否为数值型
        boolean isInteger=false;//data是否为整数
        boolean isPercent=false;//data是否为百分数
        boolean date=false;//data是否为日期
        if (data != null || "".equals(data)) {
            //判断data是否为数值型
            isNum = data.toString().matches("^(-?\\d+)(\\.\\d+)?$");
            if (isNum){
                //判断data是否为整数（小数部分是否为0）
                isInteger=data.toString().matches("^[-\\+]?[\\d]*$");
                //判断data是否为百分数（是否包含“%”）
                isPercent=data.toString().contains("%");
            }else {
                //判断data是否为日期型
                date= data.toString().matches("([0-9]{3}[1-9]|[0-9]{2}[1-9][0-9]{1}|[0-9]{1}[1-9][0-9]{2}|[1-9][0-9]{3})-(((0[13578]|1[02])-(0[1-9]|[12][0-9]|3[01]))|((0[469]|11)-(0[1-9]|[12][0-9]|30))|(02-(0[1-9]|[1][0-9]|2[0-8])))");
            }
        }

        //如果单元格内容是数值类型，设置data的类型为数值类型
        if (isNum && !isPercent) {
            // 此处设置数据格式
            DataFormat df = workbook.createDataFormat();
            if (isInteger) {
                HSSFCellStyle contextstyle = map.get("int");
                contextstyle.setDataFormat(df.getFormat("0_ "));//数据格式只显示整数
                if (data.toString().length()>15){
                    cell.setCellStyle(map.get("string"));
                    cell.setCellValue(data.toString());
                } else if(data.toString().length()>9){
                    // 设置单元格格式
                    cell.setCellStyle(contextstyle);
                    // 设置单元格内容为double类型
                    cell.setCellValue(Long.parseLong(data.toString()));
                }else {
                    // 设置单元格格式
                    cell.setCellStyle(contextstyle);
                    // 设置单元格内容为double类型
                    cell.setCellValue(Integer.parseInt(data.toString()));
                }
            }else{
                HSSFCellStyle contextstyle = map.get("double");
                contextstyle.setDataFormat(df.getFormat("0.00_ "));//保留两位小数点
                // 设置单元格格式
                cell.setCellStyle(contextstyle);
                // 设置单元格内容为double类型
                cell.setCellValue(Double.parseDouble(data.toString()));
            }
        } else {
            if (data != null && !"".equals(data)) {
                if (date){
                    String df="";
                    HSSFCellStyle contextstyle =null;
                    if (DateUtil.isLegalDate(data.toString().length(),data.toString(), Constants.DATE_FORMAT)) {
                        df=Constants.DATE_FORMAT;
                        contextstyle= map.get("dateFormat");
                    }else if (DateUtil.isLegalDate(data.toString().length(),data.toString(), Constants.DATE_FORMAT_DATE)){
                        df=Constants.DATE_FORMAT_DATE;
                        contextstyle= map.get("dateFormatDate");
                    }
                    CreationHelper createHelper = workbook.getCreationHelper();
                    short dateFormat = createHelper.createDataFormat().getFormat(df);
                    assert contextstyle != null;
                    contextstyle.setDataFormat(dateFormat);
                    cell.setCellStyle(contextstyle);
                    // 设置要求的日期格式
                    SimpleDateFormat sdf = new SimpleDateFormat(df);
                    // 设置单元格内容为字符型
                    cell.setCellValue(sdf.format(DateUtil.strToDate(data.toString(),df)));
                }else {
                    HSSFCellStyle contextstyle = map.get("string");
                    cell.setCellStyle(contextstyle);
                    // 设置单元格内容为字符型
                    cell.setCellValue(data.toString());
                }
            }else {
                HSSFCellStyle contextstyle = map.get("string");
                cell.setCellStyle(contextstyle);
                cell.setCellValue("");
            }
        }
    }

    public static Map<String,HSSFCellStyle> getCellStyles(HSSFWorkbook workbook,Font font){
        Map<String,HSSFCellStyle> map = new HashMap<>();
        for (int i = 0; i <6; i++) {
            //将内容按顺序赋给对应列对象
            //设置表内容样式
            //创建单元格，并设置值表头 设置表头居中
            HSSFCellStyle style1 = workbook.createCellStyle();
            //垂直居中
            style1.setVerticalAlignment(VerticalAlignment.CENTER);
            //水平居中
            style1.setAlignment(HorizontalAlignment.CENTER);
            style1.setBorderBottom(BorderStyle.THIN);
            style1.setBorderLeft(BorderStyle.THIN);
            style1.setBorderRight(BorderStyle.THIN);
            style1.setBorderTop(BorderStyle.THIN);
            style1.setWrapText(true);
            //设置前景填充样式
            font.setFontName("等线");
            font.setFontHeightInPoints((short) 11);//设置字体大小
            style1.setFont(font);//选择需要用到的字体格式
            switch (i){
                case 0:
                    map.put("double",style1);
                    break;
                case 1:
                    map.put("int",style1);
                    break;
                case 2:
                    map.put("dateFormat",style1);
                    break;
                case 3:
                    map.put("dateFormatDate",style1);
                    break;
                case 4:
                default:
                    map.put("string",style1);
                    break;
            }
        }
        return map;
    }


}
